﻿/*
PainlessDAL Data Access Logic helper
Copyright (C) 2010  Eduardo de Britto Castro

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see http://www.gnu.org/licenses/. 
*/
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace PainlessDAL
{
    /// <summary>
    /// Database utility class
    /// </summary>
    public static class DbHelper
    {
        /// <summary>
        /// Gets a new database connection
        /// </summary>
        /// <param name="connectionStringName">Connection string name put on the configuration file</param>
        /// <param name="open">If true automatically opens the connection</param>
        /// <returns>IDbConnection reference</returns>
        public static IDbConnection GetConnection(string connectionStringName, bool open)
        {
            ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings[connectionStringName];
            DbProviderFactory factory = DbProviderFactories.GetFactory(cs.ProviderName);
            IDbConnection con = factory.CreateConnection();
            con.ConnectionString = cs.ConnectionString;
            if (open) con.Open();
            return con;
        }
        /// <summary>
        /// Creates a new DbCommand
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction. If, for example, parametersPrefix are "?p" then parameters must be named ?p0, ?p1...</param>
        /// <param name="parametersPrefix">Ex: ?, @, :</param>
        /// <param name="values">Parameters values</param>
        /// <returns>IDbCommand reference</returns>
        public static IDbCommand CreateCommand(DbContext context, string sql, string parametersPrefix, params object[] values)
        {
            IDbCommand cmd = context.Connection.CreateCommand();
            cmd.Transaction = context.DbTransaction;
            cmd.CommandText = sql;
            for (int i = 0; i < values.Length; i++)
            {
                IDbDataParameter p = cmd.CreateParameter();
                p.ParameterName = parametersPrefix + i.ToString();
                p.Value = values[i] ?? DBNull.Value;
                cmd.Parameters.Add(p);
            }
            return cmd;
        }
        /// <summary>
        /// Creates a new DbCommand
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction.</param>
        /// <returns>IDbCommand reference</returns>
        public static IDbCommand CreateCommand(DbContext context, string sql)
        {
            IDbCommand cmd = context.Connection.CreateCommand();
            cmd.Transaction = context.DbTransaction;
            cmd.CommandText = sql;
            return cmd;
        }
        /// <summary>
        /// Extracts a value from a Data Reader
        /// </summary>
        /// <param name="reader">Data Reader</param>
        /// <param name="column">Column name</param>
        /// <param name="nullValue">Return value whether the column is null or doesn't exists</param>
        /// <returns>Column value</returns>
        public static object GetColumnValue(IDataReader reader, string column, object nullValue)
        {
            object ret = reader[column];
            if (ret is DBNull)
                return nullValue;
            else
                return ret;
        }
        /// <summary>
        /// Executes an sql statement and returns the number o frows affected
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction. If, for example, parametersPrefix are "?p" then parameters must be named ?p0, ?p1...</param>
        /// <param name="parametersPrefix">Ex: ?, @, :</param>
        /// <param name="values">Parameters values</param>
        /// <returns>Number of rows affected</returns>
        public static int ExecuteNonQuery(DbContext context, string sql, string parametersPrefix, params object[] values)
        {
            IDbCommand cmd = DbHelper.CreateCommand(context, sql, parametersPrefix, values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Executes an sql statement and returns the number o frows affected
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction.</param>
        /// <returns>Number of rows affected</returns>
        public static int ExecuteNonQuery(DbContext context, string sql)
        {
            IDbCommand cmd = DbHelper.CreateCommand(context, sql);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Executes an sql statement and returns the first column of the first row
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction. If, for example, parametersPrefix are "?p" then parameters must be named ?p0, ?p1...</param>
        /// <param name="parametersPrefix">Ex: ?, @, :</param>
        /// <param name="values">Parameters values</param>
        /// <returns>First column of the first row</returns>
        public static object ExecuteScalar(DbContext context, string sql, string parametersPrefix, params object[] values)
        {
            IDbCommand cmd = DbHelper.CreateCommand(context, sql, parametersPrefix, values);
            return cmd.ExecuteScalar();
        }
        /// <summary>
        /// Executes an sql statement and returns the first column of the first row
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">SQL instruction.</param>
        /// <returns>First column of the first row</returns>
        public static object ExecuteScalar(DbContext context, string sql)
        {
            IDbCommand cmd = DbHelper.CreateCommand(context, sql);
            return cmd.ExecuteScalar();
        }
    }
}
